package com.systemdevelopment.mainproject.Model;

import com.systemdevelopment.mainproject.View.mainUI;
import javafx.embed.swing.JFXPanel;
import javafx.scene.image.Image;
import javafx.scene.image.ImageView;

import java.io.*;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.*;
import java.util.HashSet;
import java.util.Objects;

public class Book {

	protected HashSet<Author> authorSet;		//对应AUTHOR表
	protected String ISBN;
	protected String name;			//对应TITLE列，意为书名
	protected String publisher;
	protected String publicationDate;
	protected String tab;
	protected String introduction;
	protected Image image;			//对应COVER列，封面
	protected int AllQuantity;		//馆藏数量，对应BOOK表的ALL_STORAGE列
	protected int NowQuantity;		//现有数量：= 馆藏数量-借出数量，对应BOOK表的NOW_STORAGE列
	static {
		File f=new File("./Cover/");
		if(!f.exists())
		{
			f.mkdir();
		}
	}
	public static void main(String[] args) throws SQLException, FileNotFoundException, URISyntaxException {

//		HashSet<Book> BookSet = Book.getAllBooksFromDB();		//测试1
//		for (Book bk:BookSet){
//			System.out.println(bk.ISBN);
//		}


		/*HashSet<Author> AuthorSet = new HashSet<>();		//测试2
		Author author1 = new Author("000001  ","曹雪芹","清");
		String Intro = "《红楼梦》是一部百科全书式的长篇小说。以宝黛爱情悲剧为主线，以四大家族的荣辱兴衰为背景，描绘出18世纪中国封建社会的方方面面，以及封建专制下新兴资本主义民主思想的萌动。" +
				"结构宏大、情节委婉、细节精致，人物形象栩栩如生，声口毕现，堪称中国古代小说中的经典。";
		String Isbn = "9787020002207";

		//File file = new File( Isbn + ".jpg");
		URL url= Book.class.getResource( Isbn + ".jpg");
		FileInputStream fileInputStream = new FileInputStream(url.getFile());
		Image Img = new Image(fileInputStream);

		Book book1 = new Book(AuthorSet,Isbn,"红楼梦"," 人民文学出版社","1996-12","古典小说",Intro,Img,50,30);
		book1.authorSet.add(author1);
		HashSet<Book> BookSet = new HashSet<>();
		BookSet.add(book1);
		Book.InsertBookIntoDB(BookSet);*/
	}

	public Book(){}

	public Book(HashSet<Author> authorSet, String ISBN, String name, String publisher, String publicationDate, String tab,String introduction, Image image, int allQuantity, int nowQuantity) {
		this.authorSet = authorSet;
		this.ISBN = ISBN;
		this.name = name;
		this.publisher = publisher;
		this.publicationDate = publicationDate;
		this.tab = tab;
		this.introduction = introduction;
		this.image = image;
		AllQuantity = allQuantity;
		NowQuantity = nowQuantity;
	}


	public HashSet<Author> getAuthor() {
		return authorSet;
	}

	public void setAuthor(HashSet<Author> authorSet) {
		this.authorSet = authorSet;
	}

	public String getISBN() {
		return ISBN;
	}

	public void setISBN(String ISBN) {
		this.ISBN = ISBN;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPublisher() {
		return publisher;
	}

	public void setPublisher(String publisher) {
		this.publisher = publisher;
	}

	public String getPublicationDate() {
		return publicationDate;
	}

	public void setPublicationDate(String publicationDate) {
		this.publicationDate = publicationDate;
	}

	public String getIntroduction() {
		return introduction;
	}

	public String getTab() {
		return tab;
	}

	public void setTab(String tab) {
		this.tab = tab;
	}

	public void setIntroduction(String introduction) {
		this.introduction = introduction;
	}

	public Image getImage() {
		return image;
	}

	public void setImage(Image image) {
		this.image = image;
	}

	public int getAllQuantity() {
		return AllQuantity;
	}

	public void setAllQuantity(int allQuantity) {
		AllQuantity = allQuantity;
	}

	public int getNowQuantity() {
		return NowQuantity;
	}

	public void setNowQuantity(int nowQuantity) {
		NowQuantity = nowQuantity;
	}

	@Override
	public boolean equals(Object o) {
		if (this == o) return true;
		if (o == null || getClass() != o.getClass()) return false;
		Book book = (Book) o;
		return Objects.equals(ISBN, book.ISBN) &&
				Objects.equals(name, book.name);
	}

	@Override
	public int hashCode() {
		return Objects.hash(ISBN, name);
	}





	public static HashSet<Book> getAllBooksFromDB() {//在BOOK表中检索所有书籍,以HashSet<Book>作为返回值
		HashSet<Book> BookSet = new HashSet<>();	//最后的书籍结果集
		try (Connection conn = DataBaseManager.build_conn()) {
			String sql = "select * from LIB.BOOK";

			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				String ISBN = rs.getString("ISBN");

				Clob clob = rs.getClob("INTRODUCTION");
				String Intro = null;
				if (clob != null) {
					try{
						Intro = mainUI.clobToString(clob);
					}catch (Exception e){
						e.printStackTrace();
					}

				}

				String Name = rs.getString("TITLE");

				Blob blob = rs.getBlob("COVER");
				Image Img = null;
				if (blob != null) {

					URL url = Book.class.getResource( ISBN + ".jpg");
					if(url == null) {
						url = Book.class.getResource("");
						System.out.println(url.getPath()+ISBN+".jpg");
						File file = new File(url.getPath()+ISBN+".jpg");
						if (!file.exists()) {
							file.createNewFile();
							final OutputStream os;
							os = new FileOutputStream(file);

							InputStream inputStream = blob.getBinaryStream();
							ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
							byte[] buff = new byte[100]; //buff用于存放循环读取的临时数据
							int rc;
							while ((rc = inputStream.read(buff, 0, 100)) > 0) {
								swapStream.write(buff, 0, rc);
							}
							byte[] fStreamByte = swapStream.toByteArray();
							os.write(fStreamByte);
							os.close();

						}
					}
					new JFXPanel();
					Img = new Image(url.toString());
				}

				String Publication_Date = rs.getString("PUBLICATION_DATE");
			/*SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date date = (Date) simpleDateFormat.parse(s_Date);*/

				String Tab = rs.getString("Tab");
				Integer All_Storage = rs.getInt("ALL_STORAGE");
				Integer Now_Storage = rs.getInt("NOW_STORAGE");
				String Publisher = rs.getString("PUBLISHER");

				if (ISBN != null && ISBN.length() > 0) {

					//通过ISBN在WRITE表中获取code列表，再用Code列表检索AUTHOR表。
					HashSet<Author> AuthorSet = new HashSet<>();

					String sql1 = "SELECT CODE FROM LIB.WRITE WHERE ISBN=" + "'" + ISBN + "'";
					Statement stmt1 = conn.createStatement();
					ResultSet rs1 = stmt1.executeQuery(sql1);

					while (rs1.next()) {
						String Code = rs1.getString("CODE");
						if (Code != null && Code.length() > 0) {
							Author au = Author.ReadAuthorFromDBByCode(Code);
							AuthorSet.add(au);
						}
					}
					Book book = new Book(AuthorSet, ISBN, Name, Publisher, Publication_Date,Tab, Intro, Img, All_Storage, Now_Storage);
					BookSet.add(book);
				}
			}
		} catch (SQLException | IOException Se) {
			Se.printStackTrace();
		}
		return BookSet;
	}


	public static HashSet<Book> getBookFromDBByName(String BookName)  {//用书名在BOOK表中检索书籍,可能有多个结果，以HashSet<Book>作为返回值
		HashSet<Book> BookSet = new HashSet<>();	//最后的书籍结果集
		try (Connection conn = DataBaseManager.build_conn()) {
			String sql = "select * from LIB.BOOK where TITLE = " + "'" + BookName + "'";

			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				String ISBN = rs.getString("ISBN");

				Clob clob = rs.getClob("INTRODUCTION");
				String Intro = null;
				if (clob != null) {
					try{
						Intro = mainUI.clobToString(clob);
					}catch (Exception e){
						e.printStackTrace();
					}

				}

				String Name = rs.getString("TITLE");

				Blob blob = rs.getBlob("COVER");
				Image Img = null;
				if (blob != null) {

					URL url = Book.class.getResource( ISBN + ".jpg");
					if(url == null) {
						url = Book.class.getResource("");

						File file = new File(url.getPath()+ISBN+".jpg");
						if (!file.exists()) {
							file.createNewFile();
							final OutputStream os;
							os = new FileOutputStream(file);
							InputStream inputStream = blob.getBinaryStream();
							ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
							byte[] buff = new byte[100]; //buff用于存放循环读取的临时数据
							int rc;
							while ((rc = inputStream.read(buff, 0, 100)) > 0) {
								swapStream.write(buff, 0, rc);
							}
							byte[] fStreamByte = swapStream.toByteArray();
							os.write(fStreamByte);
							os.close();

						}
					}
					new JFXPanel();
					Img = new Image(url.toString());
				}

				String Publication_Date = rs.getString("PUBLICATION_DATE");
			/*SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date date = (Date) simpleDateFormat.parse(s_Date);*/

				String Tab = rs.getString("Tab");
				Integer All_Storage = rs.getInt("ALL_STORAGE");
				Integer Now_Storage = rs.getInt("NOW_STORAGE");
				String Publisher = rs.getString("PUBLISHER");

				if (ISBN != null && ISBN.length() > 0) {

					//通过ISBN在WRITE表中获取code列表，再用Code列表检索AUTHOR表。
					HashSet<Author> AuthorSet = new HashSet<>();

					String sql1 = "SELECT CODE FROM LIB.WRITE WHERE ISBN=" + "'" + ISBN + "'";
					Statement stmt1 = conn.createStatement();
					ResultSet rs1 = stmt1.executeQuery(sql1);

					while (rs1.next()) {
						String Code = rs1.getString("CODE");
						if (Code != null && Code.length() > 0) {
							Author au = Author.ReadAuthorFromDBByCode(Code);
							AuthorSet.add(au);
						}
					}
					Book book = new Book(AuthorSet, ISBN, Name, Publisher, Publication_Date,Tab, Intro, Img, All_Storage, Now_Storage);
					BookSet.add(book);
				}
			}
		} catch (SQLException | IOException Se) {
			Se.printStackTrace();
		}
		return BookSet;
	}

	public static Clob getCoverByISBN(String isbn)
	{
		String sql = "select * from LIB.BOOK where ISBN = " + "'" + isbn + "'";
		Connection conn = null ;
		try {
			conn = DataBaseManager.build_conn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				Clob clob=rs.getClob(3);
				return clob;
			}



		} catch (SQLException e) {
		e.printStackTrace();
		}
		return null;
	}
	public static String getNameByISBN(String isbn)
	{
		String sql = "select * from LIB.BOOK where ISBN = " + "'" + isbn + "'";
		Connection conn = null ;
		try {
			conn = DataBaseManager.build_conn();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next())
			{
				String title=rs.getString(4);
				return title;
			}



		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public void InsertIntoWrite() throws SQLException {	//将当前书籍对象所包含的Write信息插入到Write表中
		Connection conn = null;
		try {
			conn = DataBaseManager.build_conn();
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			for(Author au:authorSet){
				String code = au.getCode();
				String sql = "INSERT INTO LIB.WRITE VALUES("+ "'"+ ISBN + "'" + "," + "'" + code + "')";
				System.out.println(sql);
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
			conn.commit();
		}catch (SQLException Se){
			Se.printStackTrace();
		}finally {
			if (conn != null) {
				conn.setAutoCommit(true); // 还原事务的提交方式
				conn.close();
			}
		}
	}

	public static void InsertBookIntoDB(Book... b) throws SQLException {
		Connection conn = null;
		try {
			conn = DataBaseManager.build_conn();
			String sql = "INSERT INTO LIB.BOOK VALUES(?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);

			for (Book bk : b) {
				conn.setAutoCommit(false);

				String Isbn = bk.getISBN();

				String Intro = bk.getIntroduction();
				Clob clob = null;
				if (Intro != null && Intro.length() > 0) {
					clob = oracle.sql.CLOB.createTemporary(conn, false,oracle.sql.CLOB.DURATION_SESSION);
					clob.setString(1, Intro);
				}

				Image Img = bk.getImage();
				FileInputStream fileInputStream = null;
				if (Img != null) {
					URL url= Book.class.getResource( Isbn + ".jpg");
					assert url != null;
					fileInputStream = new FileInputStream(url.getFile());
				}

				String Name = bk.getName();
				String Publication_Date = bk.getPublicationDate();
				String Tab = bk.getTab();
				Integer All_Storage = bk.getAllQuantity();
				Integer Now_Storage = bk.getNowQuantity();
				String Publisher = bk.getPublisher();

				pstmt.setString(1, Isbn);
				pstmt.setClob(2, clob);
				pstmt.setBlob(3, fileInputStream);
				pstmt.setString(4, Name);
				pstmt.setString(5, Publication_Date);
				pstmt.setString(6, Tab);
				pstmt.setInt(7, All_Storage);
				pstmt.setInt(8, Now_Storage);
				pstmt.setString(9, Publisher);

				pstmt.executeUpdate();
				conn.commit();

				HashSet<Author> authorSet = bk.getAuthor();//插入作者
				Author.InsertAuthorIntoDB(authorSet);
				bk.InsertIntoWrite();
				//conn.commit();
			}
		} catch (SQLException Se) {
			Se.printStackTrace();
			assert conn != null;
			conn.rollback();	//回滚
		} catch (IOException IOe) {
			IOe.printStackTrace();
		}
	}
	public static void InsertBookIntoDB(HashSet<Book> Bookset) throws SQLException {
			for (Book b:Bookset
				 ) {
				InsertBookIntoDB(b);
			}
	}

	public static void DeleteBookFromDB(String ISBN) throws SQLException {//从数据库BOOK表中删除一行，参数是ISBN
		Connection conn = null;
		try {
			conn = DataBaseManager.build_conn();
			String sql = "DELETE FROM LIB.BOOK WHERE ISBN = '" + ISBN +"'";

			Statement stmt = conn.createStatement();
			int count = stmt.executeUpdate(sql);
		}catch (SQLException Se){
			Se.printStackTrace();
		}finally {
			conn.close();
		}
	}
}